﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.TreeView;
using System.Drawing.Printing;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
using static System.Runtime.InteropServices.JavaScript.JSType;
using Microsoft.VisualBasic;
using System.Net;
using System.Numerics;
using System.Xml.Linq;
using System.Security.Principal;

namespace Bank_database_system
{
    /*********************************************************
     * 
     *财务部门
     *
     *********************************************************/
    public static class Finance
    {
        //数据库连接字符串
        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";

        //根据支行名称得到支行ID
        public static string GetBranchID(string branch_name)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT BRANCH_ID FROM BRANCH WHERE BRANCH_NAME = :branch_name";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter("branch_name", branch_name));

                        string result = Convert.ToString(command.ExecuteScalar());
                        if (result != null && result != string.Empty)
                        {
                            return result;
                        }
                        else
                        {
                            MessageBox.Show("获取支行ID失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return string.Empty; //出错返回空值
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("获取支行ID异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return string.Empty; //出错返回空值
            }
        }

        //得到本地IP地址
        public static string GetLocalIPAddress()
        {
            string localIP = "";
            foreach (var address in Dns.GetHostEntry(Dns.GetHostName()).AddressList)
            {
                if (address.AddressFamily == System.Net.Sockets.AddressFamily.InterNetwork)
                {
                    localIP = address.ToString();
                    break;
                }
            }
            return localIP;
        }



        //以下为资金调度部分

        /********************************************************************* 
         *通过支行ID查询支行信息（已废弃）
         *参数：
         *string branch_ID 支行ID
         *返回查询结果表
         **********************************************************************/
        public static DataTable Selcet_Branch_By_ID(string branch_ID)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT * FROM BRANCH WHERE BRANCH_ID = :branch_ID";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter(":branch_ID", OracleDbType.Varchar2)).Value = branch_ID;

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); //出错返回空表
            }
        }

        /********************************************************************* 
         *以分页方式查询所有支行信息，便于显示（已废弃）
         *参数：
         *int PageNumber 当前页码
         *int PageSize   页大小
         *返回查询结果表
         **********************************************************************/
        public static DataTable Selcet_All_Branch(int PageNumber, int PageSize)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();
                    int offset = (PageNumber - 1) * PageSize;//得到偏移量

                    string query_string = @"SELECT * FROM (
                                              SELECT a.*, ROWNUM rnum
                                              FROM (
                                                SELECT * FROM BRANCH
                                                ORDER BY BRANCH_ID
                                              ) a
                                              WHERE ROWNUM <= :MaxRow
                                            )
                                            WHERE rnum >= :MinRow";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter("MaxRow", offset + PageSize));
                        command.Parameters.Add(new OracleParameter("MinRow", offset));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据库连接失败，请检查网络连接！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); //出错返回空表
            }
        }

        /********************************************************************* 
         *支行间资金调度
         *参数：
         *string my_branch_ID     本行支行ID
         *string target_branch_ID 目标银行支行ID
         *string currency_kind    货币类型
         *decimal amount          调度金额
         *无返回值
         **********************************************************************/
        public static void Funds_Transport(string my_branch_ID, string target_branch_ID, string currency_kind, decimal amount)
        {
            if(my_branch_ID == target_branch_ID)
            {
                MessageBox.Show("目标支行与本支行相同，请重新输入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    /*检查my_branch_ID是否存在*/
                    string check_my_branch_string = @"SELECT COUNT(*) FROM BRANCH WHERE BRANCH_ID = :my_branch_ID ";

                    using (OracleCommand check_my_branch_ID = new OracleCommand(check_my_branch_string, connection))
                    {
                        check_my_branch_ID.Parameters.Add(new OracleParameter("my_branch_ID", my_branch_ID));
                        int my_branch_exists = Convert.ToInt32(check_my_branch_ID.ExecuteScalar());
                        if (my_branch_exists <= 0)
                        {
                            MessageBox.Show("当前支行ID不存在，资金调度失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                        else
                        {
                            /*检查target_branch_ID是否存在*/
                            string check_target_branch_string = @"SELECT COUNT(*) FROM BRANCH WHERE BRANCH_ID = :target_branch_ID ";

                            using (OracleCommand check_target_branch_ID = new OracleCommand(check_target_branch_string, connection))
                            {
                                check_target_branch_ID.Parameters.Add(new OracleParameter("target_branch_ID", target_branch_ID));
                                int target_branch_exists = Convert.ToInt32(check_target_branch_ID.ExecuteScalar());
                                if (target_branch_exists <= 0)
                                {
                                    MessageBox.Show("目标支行ID不存在，资金调度失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                }
                                else
                                {
                                    /*检查货币类型是否存在*/
                                    string check_currency_kind_string = @"SELECT COUNT(*) FROM CURRENCY WHERE CURRENCY_KIND = :currency_kind ";

                                    using (OracleCommand check_currency_kind = new OracleCommand(check_currency_kind_string, connection))
                                    {
                                        check_currency_kind.Parameters.Add(new OracleParameter("currency_kind", currency_kind));
                                        int currency_kind_exists = Convert.ToInt32(check_currency_kind.ExecuteScalar());
                                        if (currency_kind_exists <= 0)
                                        {
                                            MessageBox.Show("货币类型不存在，资金调度失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                        }
                                        else
                                        {
                                            /*检查本支行货币数量是否充足*/
                                            string check_amount_string = @"SELECT AMOUNT FROM FUND_STORAGE WHERE CURRENCY_KIND = :currency_kind AND BRANCH_ID = :my_branch_ID ";

                                            using (OracleCommand check_amount = new OracleCommand(check_amount_string, connection))
                                            {
                                                check_amount.Parameters.Add(new OracleParameter("currency_kind", currency_kind));
                                                check_amount.Parameters.Add(new OracleParameter("my_branch_ID", my_branch_ID));
                                                decimal storage = Convert.ToDecimal(check_amount.ExecuteScalar());
                                                if (storage < amount)
                                                {
                                                    MessageBox.Show("本支行货币数量不足，资金调度失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                }
                                                else
                                                {
                                                    /*如果此次调度合法，则从本支行取款，运送给目标支行存款*/

                                                    //修改目标支行STORAGE
                                                    string update_target_fund_storage = @"UPDATE FUND_STORAGE SET AMOUNT = AMOUNT + :amount
                                                                                          WHERE BRANCH_ID = :target_branch_ID AND CURRENCY_KIND = :currency_kind ";

                                                    using (OracleCommand update_target = new OracleCommand(update_target_fund_storage, connection))
                                                    {
                                                        update_target.Parameters.Add(new OracleParameter("amount", amount));
                                                        update_target.Parameters.Add(new OracleParameter("target_branch_ID", target_branch_ID));
                                                        update_target.Parameters.Add(new OracleParameter("currency_kind", currency_kind));
                                                        update_target.ExecuteNonQuery();
                                                    }

                                                    //修改本支行STORAGE
                                                    string update_my_fund_storage = @"UPDATE FUND_STORAGE SET AMOUNT = AMOUNT - :amount
                                                                                      WHERE BRANCH_ID = :my_branch_ID AND CURRENCY_KIND = :currency_kind ";

                                                    using (OracleCommand update_my = new OracleCommand(update_my_fund_storage, connection))
                                                    {
                                                        update_my.Parameters.Add(new OracleParameter("amount", amount));
                                                        update_my.Parameters.Add(new OracleParameter("my_branch_ID", my_branch_ID));
                                                        update_my.Parameters.Add(new OracleParameter("currency_kind", currency_kind));
                                                        update_my.ExecuteNonQuery();
                                                    }

                                                    /*//得到本支行银行卡号
                                                    string my_card = string.Empty;
                                                    string get_my_card_string = @"SELECT BRANCH_CARD FROM BRANCH WHERE BRANCH_ID = :my_branch_ID ";

                                                    using (OracleCommand get_my_card = new OracleCommand(get_my_card_string, connection))
                                                    {
                                                        get_my_card.Parameters.Add(new OracleParameter("my_branch_ID", my_branch_ID));
                                                        my_card = Convert.ToString(get_my_card.ExecuteScalar());
                                                    }

                                                    //得到目标支行银行卡号
                                                    string target_card = string.Empty;
                                                    string get_target_card_string = @"SELECT BRANCH_CARD FROM BRANCH WHERE BRANCH_ID = :target_branch_ID ";

                                                    using (OracleCommand get_target_card = new OracleCommand(get_target_card_string, connection))
                                                    {
                                                        get_target_card.Parameters.Add(new OracleParameter("target_branch_ID", target_branch_ID));
                                                        target_card = Convert.ToString(get_target_card.ExecuteScalar());
                                                    }

                                                    if(my_card != string.Empty && my_card != null && target_card != string.Empty && target_card != null)
                                                    {
                                                        //如果两支行卡号合法
                                                        string my_result = Withdrawal_Operations.Withdrawal(my_card, amount, my_branch_ID, currency_kind, GetLocalIPAddress(), operetor_ID);
                                                        string target_result = Deposit_Operations.Deposit(target_card, amount, target_branch_ID, currency_kind, GetLocalIPAddress(), operetor_ID);
                                                        if (my_result == "取款成功" && target_result == "存款成功")
                                                        {
                                                            MessageBox.Show("调度成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                        }
                                                        else
                                                        {
                                                            MessageBox.Show($"调度失败!\n取款信息：{my_result}\n存款信息：{target_result}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                        }
                                                    }
                                                    else
                                                    {
                                                        MessageBox.Show("银行卡号获取异常，调度失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                    }*/

                                                    MessageBox.Show("调度成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("资金调度异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        /********************************************************************* 
         *显示本支行货币数量
         *参数：
         *string branch_ID     本行支行ID
         *返回查询结果表
         **********************************************************************/
        public static DataTable GetFundStorage(string branch_ID)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT CURRENCY_KIND AS 货币类型, AMOUNT AS 数量, RATE AS 当前汇率
                                            FROM FUND_STORAGE NATURAL JOIN CURRENCY
                                            WHERE BRANCH_ID = :branch_ID ";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter(":branch_ID", branch_ID));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("获取货币数量异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); //出错返回空表
            }
        }








        //以下为工资发放部分

        /********************************************************************* 
         *以分页方式查询员工信息
         *参数：
         *string staff_ID        员工ID
         *string staff_name      员工姓名
         *string department      部门
         *string branch_ID       本行支行ID
         *int PageNumber         当前页码
         *int PageSize           页大小
         *返回查询结果表
         **********************************************************************/
        public static DataTable Select_Staff(string staff_ID, string staff_name, string department, string branch_ID, int PageNumber, int PageSize)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();
                    int offset = (PageNumber - 1) * PageSize;//得到偏移量

                    string query_string = @"SELECT STAFF_ID AS ID, NAME AS 姓名, DEPARTMENT AS 部门 ,SALARY AS 月工资, SALARY_CARD_ID AS 收款账户, SALARY_STATE AS 工资发放情况
                                              FROM (
                                              SELECT a.*, ROWNUM rnum
                                              FROM (
                                                SELECT * 
                                                FROM STAFF NATURAL JOIN PERSONAL_INFORMATION
                                                WHERE WORKING_CONDITION = '在职' AND BRANCH_ID = :branch_ID ";

                    if (staff_ID != string.Empty)
                    {
                        query_string += "AND STAFF_ID LIKE :staff_id || '%'";
                    }
                    if (department != string.Empty)
                    {
                        query_string += "AND DEPARTMENT = :department ";
                    }
                    if (staff_name != string.Empty)
                    {
                        query_string += "AND NAME LIKE :staff_name || '%'";
                    }

                    query_string += @"ORDER BY STAFF_ID) a 
                                    WHERE ROWNUM <= :MaxRow
                                    )WHERE rnum >= :MinRow ";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter("branch_id", branch_ID));
                        if (staff_ID != string.Empty)
                        {
                        command.Parameters.Add(new OracleParameter("staff_id", staff_ID));
                        }
                        if (department != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("department", department));
                        }
                        if (staff_name != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("staff_name", staff_name));
                        }
                        command.Parameters.Add(new OracleParameter("MaxRow", offset + PageSize));
                        command.Parameters.Add(new OracleParameter("MinRow", offset + 1));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            if (dataTable.Rows.Count == 0)
                            {
                                MessageBox.Show("查询结果为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("查询员工信息异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); //出错返回空表
            }
        }

        /********************************************************************* 
         *得到员工信息总页数
         *参数：
         *string staff_ID        员工ID
         *string staff_name      员工姓名
         *string department      部门
         *string branch_ID       本行支行ID
         *返回员工信息总页数
         **********************************************************************/
        public static int GetStaffPageNumber(string staff_ID, string staff_name, string department, string branch_ID)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT COUNT(*) 
                                            FROM STAFF NATURAL JOIN PERSONAL_INFORMATION
                                            WHERE WORKING_CONDITION = '在职' AND BRANCH_ID = :branch_ID ";

                    if (staff_ID != string.Empty)
                    {
                        query_string += "AND STAFF_ID LIKE :staff_id || '%'";
                    }
                    if (department != string.Empty)
                    {
                        query_string += "AND DEPARTMENT = :department ";
                    }
                    if (staff_name != string.Empty)
                    {
                        query_string += "AND NAME LIKE :staff_name || '%'";
                    }

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        command.Parameters.Add(new OracleParameter("branch_id", branch_ID));
                        if (staff_ID != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("staff_id", staff_ID));
                        }
                        if (department != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("department", department));
                        }
                        if (staff_name != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("staff_name", staff_name));
                        }

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            int total = 0;
                            object result = command.ExecuteScalar();
                            if (result != DBNull.Value)
                            {
                                total = Convert.ToInt32(result);
                            }
                            if((total % 财务首页.PageSize) == 0)
                            {
                                return (total / 财务首页.PageSize);
                            }
                            else
                            {
                                return (total / 财务首页.PageSize) + 1;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("获取员工信息页数异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return -1; //出错返回-1
            }
        }

        /********************************************************************* 
         *发放工资
         *参数：
         *List<string> staff_ID     发放工资的员工ID数组
         *string branch_ID          支行ID
         *string operator_ID         操作员ID
         *无返回值
         **********************************************************************/
        public static void Pay_Wages(List<string> staff_ID_list, string branch_ID, string operator_ID)
        {
            if (staff_ID_list.Count == 0)
            {
                MessageBox.Show("员工ID数组为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            int success_count = 0;
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open(); // 确保连接已打开

                    foreach (string staff_ID in staff_ID_list)
                    {
                        if (!string.IsNullOrEmpty(staff_ID))
                        {
                            // 查询本支行是否有此员工
                            string staff_exists = "SELECT COUNT(*) FROM STAFF WHERE STAFF_ID = :staff_ID AND BRANCH_ID = :branch_ID ";

                            using (OracleCommand check_staff_ID = new OracleCommand(staff_exists, connection))
                            {
                                check_staff_ID.Parameters.Add(new OracleParameter("staff_ID", staff_ID));
                                check_staff_ID.Parameters.Add(new OracleParameter("branch_ID", branch_ID));

                                int staffCount = Convert.ToInt32(check_staff_ID.ExecuteScalar());

                                // 若不存在该员工，则提示
                                if (staffCount <= 0)
                                {
                                    MessageBox.Show($"成功发放数量：{success_count}\n提示：员工 {staff_ID} 不存在！终止发放", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                    break; // 终止发放
                                }
                                else
                                {
                                    //检查本月工资是否已发放
                                    string whether_paid = "SELECT COUNT(*) FROM STAFF WHERE STAFF_ID = :staff_ID AND SALARY_STATE = '未发放'";

                                    using (OracleCommand check_whether_paid = new OracleCommand(whether_paid, connection))
                                    {
                                        check_whether_paid.Parameters.Add(new OracleParameter("staff_ID", staff_ID));

                                        int wageCount = Convert.ToInt32(check_whether_paid.ExecuteScalar());
                                        //该员工本月工资已发放，则提示
                                        if (wageCount != 1)
                                        {
                                            MessageBox.Show($"成功发放数量：{success_count}\n提示：员工 {staff_ID} 工资已发放！终止发放", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                            break; // 终止发放
                                        }
                                        else
                                        {
                                            //得到该员工的工资卡号以及月工资
                                            string staff_card_ID = string.Empty;
                                            decimal amount = 0;
                                            string get_staff_info = "SELECT SALARY, SALARY_CARD_ID FROM STAFF WHERE STAFF_ID = :staff_ID ";

                                            using (OracleCommand get_staff = new OracleCommand(get_staff_info, connection))
                                            {
                                                get_staff.Parameters.Add(new OracleParameter("staff_ID", staff_ID));

                                                using (OracleDataReader reader = get_staff.ExecuteReader())
                                                {
                                                    if (reader.Read()) // 确保有结果
                                                    {
                                                        // 读取数据并赋值
                                                        amount = reader.GetDecimal(reader.GetOrdinal("SALARY"));
                                                        staff_card_ID = reader.GetString(reader.GetOrdinal("SALARY_CARD_ID"));
                                                    }
                                                }
                                            }

                                            //得到支行卡号
                                            string branch_card_ID = string.Empty;
                                            string get_branch_info = "SELECT BRANCH_CARD FROM BRANCH WHERE BRANCH_ID = :branch_ID ";

                                            using (OracleCommand get_branch = new OracleCommand(get_branch_info, connection))
                                            {
                                                get_branch.Parameters.Add(new OracleParameter("branch_ID", branch_ID));

                                                branch_card_ID = Convert.ToString(get_branch.ExecuteScalar());
                                            }

                                            //发放工资
                                            string IP = GetLocalIPAddress();
                                            if (branch_card_ID != string.Empty && branch_card_ID != null && staff_card_ID != string.Empty && IP != string.Empty && operator_ID != string.Empty)
                                            {
                                                string result = Transfer.TransferBusiness(branch_card_ID, staff_card_ID, IP, amount, operator_ID);
                                                if (result != "转账成功，5分钟后到账")
                                                {
                                                    MessageBox.Show($"成功发放数量：{success_count}\n提示：员工 {staff_ID} 转账异常\n信息：{result}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                    break; // 终止发放
                                                }
                                                else
                                                {
                                                    //修改该员工工资发放状态
                                                    string update_salary_state = @"UPDATE STAFF SET SALARY_STATE = '已发放'
                                                                                   WHERE BRANCH_ID = :branch_ID AND STAFF_ID = :staff_ID ";

                                                    using (OracleCommand update_state = new OracleCommand(update_salary_state, connection))
                                                    {
                                                        update_state.Parameters.Add(new OracleParameter("branch_ID", branch_ID));
                                                        update_state.Parameters.Add(new OracleParameter("staff_ID", staff_ID));
                                                        update_state.ExecuteNonQuery();
                                                    }

                                                    success_count++;
                                                }
                                            }
                                            else
                                            {
                                                MessageBox.Show($"成功发放数量：{success_count}\n提示：员工 {staff_ID} 信息异常", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                                                break; // 终止发放
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    if (success_count == staff_ID_list.Count)
                    {
                        MessageBox.Show($"预期发放数量：{staff_ID_list.Count}\n成功发放数量：{success_count}\n发放成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                Console.WriteLine("StackTrace: " + ex.StackTrace);
                MessageBox.Show($"成功发放数量：{success_count}\n出现异常，发放终止！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }








        //以下为流水查询部分

        /********************************************************************* 
         *以分页方式查询本银行流水（存款与取款）
         *参数：
         *string branch_ID       本行支行ID
         *string BeginDate       起始日期
         *string EndDate         终止日期
         *int PageNumber         当前页码
         *int PageSize           页大小
         *返回查询结果表
         **********************************************************************/
        public static DataTable Select_Transaction(string branch_ID, string BeginDate, string EndDate, int PageNumber, int PageSize)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();
                    int offset = (PageNumber - 1) * PageSize;//得到偏移量

                    string query_string = @"SELECT TRANSACTION_ID AS 交易ID, CREDIT_CARD_NUMBERS AS 卡号, TRANSACTION_COMMENT AS 类型, TRANSACTION_TIME AS 时间, TRANSACTION_CURRENCY_TYPE AS 币种, AMOUNT AS 金额
                                            FROM (
                                              SELECT a.*, ROWNUM rnum
                                              FROM (
                                                SELECT * 
                                                FROM TRANSACTION_HISTORY
                                                WHERE (TRANSACTION_COMMENT = '存款' 
                                                    OR TRANSACTION_COMMENT = '取款')
                                                    ";

                    if(BeginDate == EndDate && BeginDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) = TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))";
                    }
                    else if(BeginDate != string.Empty && EndDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) >= TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))
                                          AND TRUNC(TRANSACTION_TIME) <= TRUNC(TO_DATE(:EndDate , 'YYYY/MM/DD'))";
                    }
                    
                    query_string += @"AND CREDIT_CARD_NUMBERS IN 
                                         (SELECT CREDIT_CARD_NUMBERS 
                                          FROM ACCOUNT
                                          WHERE BRANCH_ID = :branch_ID)
                                      ORDER BY TRANSACTION_TIME) a
                                      WHERE ROWNUM <= :MaxRow)
                                      WHERE rnum >= :MinRow ";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        if (BeginDate == EndDate && BeginDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                        }
                        else if (BeginDate != string.Empty && EndDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                            command.Parameters.Add(new OracleParameter("EndDate", EndDate));
                        }

                        command.Parameters.Add(new OracleParameter("branch_ID", branch_ID));
                        command.Parameters.Add(new OracleParameter("MaxRow", offset + PageSize));
                        command.Parameters.Add(new OracleParameter("MinRow", offset + 1));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            DataTable dataTable = new DataTable();
                            dataTable.Load(reader);
                            if(dataTable.Rows.Count == 0)
                            {
                                MessageBox.Show("查询结果为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            }
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("查询流水异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return new DataTable(); //出错返回空表
            }
        }

        /********************************************************************* 
         *得到银行流水总页数
         *参数：
         *string branch_ID       本行支行ID
         *string BeginDate       起始日期
         *string EndDate         终止日期
         *返回银行指定时间段内的流水总页数
         **********************************************************************/
        public static int GetTransactionPageNumber(string branch_ID, string BeginDate, string EndDate)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT COUNT(*) 
                                            FROM TRANSACTION_HISTORY
                                            WHERE (TRANSACTION_COMMENT = '存款' 
                                                OR TRANSACTION_COMMENT = '取款')
                                                ";

                    if (BeginDate == EndDate && BeginDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) = TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))";
                    }
                    else if (BeginDate != string.Empty && EndDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) >= TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))
                                          AND TRUNC(TRANSACTION_TIME) <= TRUNC(TO_DATE(:EndDate , 'YYYY/MM/DD'))";
                    }

                    query_string += @"AND CREDIT_CARD_NUMBERS IN 
                                         (SELECT CREDIT_CARD_NUMBERS 
                                          FROM ACCOUNT
                                          WHERE BRANCH_ID = :branch_ID )";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        if (BeginDate == EndDate && BeginDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                        }
                        else if (BeginDate != string.Empty && EndDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                            command.Parameters.Add(new OracleParameter("EndDate", EndDate));
                        }
                        command.Parameters.Add(new OracleParameter("branch_ID", branch_ID));

                        using (OracleDataReader reader = command.ExecuteReader())
                        {
                            int total = 0;
                            object result = command.ExecuteScalar();
                            if (result != DBNull.Value)
                            {
                                total = Convert.ToInt32(result);
                            }
                            if ((total % 财务首页.PageSize) == 0)
                            {
                                return (total / 财务首页.PageSize);
                            }
                            else
                            {
                                return (total / 财务首页.PageSize) + 1;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("获取流水页数异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return -1; //出错返回-1
            }
        }

        /********************************************************************* 
         *得到银行总支出（取款）
         *参数：
         *string branch_ID     本行支行ID
         *string BeginDate     起始日期
         *string EndDate       终止日期
         *返回银行指定时间段内的总支出（RMB）
         **********************************************************************/
        public static decimal GetExpenses(string branch_ID, string BeginDate, string EndDate)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT SUM(th.AMOUNT * c.RATE) 
                                            FROM TRANSACTION_HISTORY th
                                            JOIN CURRENCY c
                                            ON th.TRANSACTION_CURRENCY_TYPE = c.CURRENCY_KIND
                                            WHERE th.TRANSACTION_COMMENT = '取款'
                                            ";

                    if (BeginDate == EndDate && BeginDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(th.TRANSACTION_TIME) = TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))";
                    }
                    else if (BeginDate != string.Empty && EndDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(th.TRANSACTION_TIME) >= TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))
                                          AND TRUNC(th.TRANSACTION_TIME) <= TRUNC(TO_DATE(:EndDate , 'YYYY/MM/DD'))";
                    }

                    query_string += @"AND th.CREDIT_CARD_NUMBERS IN 
                                         (SELECT CREDIT_CARD_NUMBERS 
                                          FROM ACCOUNT
                                          WHERE BRANCH_ID = :branch_ID )";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        if (BeginDate == EndDate && BeginDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                        }
                        else if (BeginDate != string.Empty && EndDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                            command.Parameters.Add(new OracleParameter("EndDate", EndDate));
                        }
                        command.Parameters.Add(new OracleParameter("branch_ID", branch_ID));

                        decimal total = 0;
                        object result = command.ExecuteScalar();
                        if(result != DBNull.Value)
                        {
                            total = Convert.ToDecimal(result);
                        }
                        return total;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("获取支出异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return -1; //出错返回-1
            }
        }

        /********************************************************************* 
         *得到银行本月总收入（存款）
         *参数：
         *string branch_ID     本行支行ID
         *string BeginDate     起始日期
         *string EndDate       终止日期
         *返回银行指定时间段内的总收入（RMB）
         **********************************************************************/
        public static decimal GetIncome(string branch_ID, string BeginDate, string EndDate)
        {
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    string query_string = @"SELECT SUM(AMOUNT) 
                                            FROM TRANSACTION_HISTORY
                                            WHERE (TRANSACTION_COMMENT = '存款' OR TRANSACTION_COMMENT = '定期存款')
                                            ";

                    if (BeginDate == EndDate && BeginDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) = TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))";
                    }
                    else if (BeginDate != string.Empty && EndDate != string.Empty)
                    {
                        query_string += @"AND TRUNC(TRANSACTION_TIME) >= TRUNC(TO_DATE(:BeginDate , 'YYYY/MM/DD'))
                                          AND TRUNC(TRANSACTION_TIME) <= TRUNC(TO_DATE(:EndDate , 'YYYY/MM/DD'))";
                    }

                    query_string += @"AND CREDIT_CARD_NUMBERS IN 
                                         (SELECT CREDIT_CARD_NUMBERS 
                                          FROM ACCOUNT
                                          WHERE BRANCH_ID = :branch_ID )";

                    using (OracleCommand command = new OracleCommand(query_string, connection))
                    {
                        if (BeginDate == EndDate && BeginDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                        }
                        else if (BeginDate != string.Empty && EndDate != string.Empty)
                        {
                            command.Parameters.Add(new OracleParameter("BeginDate", BeginDate));
                            command.Parameters.Add(new OracleParameter("EndDate", EndDate));
                        }
                        command.Parameters.Add(new OracleParameter("branch_ID", branch_ID));

                        decimal total = 0;
                        object result = command.ExecuteScalar();
                        if (result != DBNull.Value)
                        {
                            total = Convert.ToDecimal(result);
                        }
                        return total;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("ErrorMessage: " + ex.Message);
                MessageBox.Show("获取收入异常！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return -1; //出错返回-1
            }
        }
    }
}
